﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP07
    Inherits System.Web.UI.Page

    Dim Report As Object
    Public ScriptText As String
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP07")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        If Session("users") Is Nothing Then
            Response.Redirect("../LoginForm.aspx")
        End If


        Getdata()


    End Sub


    Function Getdata() As Decimal

        Dim dt As New DataTable
        dt = GetData2(Val(Session("year1")), Session("version1"), Val(Session("year2")), Session("version2"), Val(Session("year3")), Session("version3"), Val(Session("year4")), Session("version4"), Val(Session("year5")), Session("version5"))

        'Response.Write(sql)
        'Exit Function
        'adapter.SearchReport(dt, sql)



        'Return 1
        'Exit Function
        dt.TableName = "R07DT"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local

        ReportViewer1.LocalReport.ReportPath = "Report\R07.rdlc"

        'If Val(Request("a")) = 0 Then
        '    'ReportViewer1.LocalReport.ReportPath = "Report\R07.rdlc"

        'ElseIf Val(Request("a")) = 1 Then
        '    'ReportViewer1.LocalReport.ReportPath = "Report\R07_1.rdlc"
        'ElseIf Val(Request("a")) = 2 Then
        '    'ReportViewer1.LocalReport.ReportPath = "Report\R07_2.rdlc"
        'ElseIf Val(Request("a")) = 3 Then
        '    'ReportViewer1.LocalReport.ReportPath = "Report\R07_3.rdlc"
        'End If


        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R07DT")))
        ReportViewer1.DocumentMapCollapsed = True



        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version1")
        Dim users As String = Session("users")

        Dim a As String = Val(Request("a"))

        '=======================================================================================
        Dim year1 As String = "แผน V" & Session("version1") & " ปี " & Session("year1")
        Dim year2 As String = "แผน V" & Session("version2") & " ปี " & Session("year2")
        Dim year3 As String = "แผน V" & Session("version3") & " ปี " & Session("year3")
        Dim year4 As String = "แผน V" & Session("version4") & " ปี " & Session("year4")
        Dim year5 As String = "แผน V" & Session("version5") & " ปี " & Session("year5")

        Dim p1, p2, p3, y1, y2, y3, y4, y5, Apara As ReportParameter

        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        If Session("version1") <> "" And Session("year1") <> "" Then
            y1 = New ReportParameter("year1", year1)
        Else
            y1 = New ReportParameter("year1", " ")
        End If

        If Session("version2") <> "" And Session("year2") <> "" Then
            y2 = New ReportParameter("year2", year2)
        Else
            y2 = New ReportParameter("year2", " ")
        End If

        If Session("version3") <> "" And Session("year3") <> "" Then
            y3 = New ReportParameter("year3", year3)
        Else
            y3 = New ReportParameter("year3", " ")
        End If

        If Session("version4") <> "" And Session("year4") <> "" Then
            y4 = New ReportParameter("year4", year4)
        Else
            y4 = New ReportParameter("year4", " ")
        End If

        If Session("version5") <> "" And Session("year5") <> "" Then
            y5 = New ReportParameter("year5", year5)
        Else
            y5 = New ReportParameter("year5", " ")
        End If

        '=======================================================================================

        Apara = New ReportParameter("a", Val(Request("a")))


        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.SetParameters(y1)
        ReportViewer1.LocalReport.SetParameters(y2)
        ReportViewer1.LocalReport.SetParameters(y3)
        ReportViewer1.LocalReport.SetParameters(y4)
        ReportViewer1.LocalReport.SetParameters(y5)

        ReportViewer1.LocalReport.SetParameters(Apara)
        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function


    Function GetData2(planyear1 As Decimal, version1 As String, planyear2 As Decimal, version2 As String, planyear3 As Decimal, version3 As String, planyear4 As Decimal, version4 As String, planyear5 As Decimal, version5 As String) As DataTable

        Dim dt As New DataTable
        dt.Columns.Add("fundcentercode", Type.GetType("System.String"))
        dt.Columns.Add("cmmtcode", Type.GetType("System.String"))
        dt.Columns.Add("Description", Type.GetType("System.String"))
        dt.Columns.Add("project", Type.GetType("System.String"))
        dt.Columns.Add("amount1", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount2", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount3", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount4", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount5", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount_diff", Type.GetType("System.Decimal"))
        dt.Columns.Add("amount_diff_per", Type.GetType("System.Decimal"))
        Dim adapter As New Data_ReportTableAdapters.R07TableAdapter
        Dim datatable As New Data_Report.R07DataTable
        Dim sql As String
        sql = " select distinct  (budgetdetails.fundcenter_fundcentercode + ' ' + isnull(fundcenters.description,'')) as fundcentercode ,case when BudgetDetails.project_projectcode is null then 'งบประมาณประจำ' else (case LEFT(BudgetDetails.project_projectcode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) end as project, cmmtCode , Cmmts.description"
        sql += " , CONVERT(VARCHAR,FctH.grouppath),budgetdetails.Id"

        ''''sum amount on version and year

        If Session("version1") <> "" And Session("year1") <> "" Then
            sql += " ,(SELECT ISNULL(SUM(b2.Amount),0)"
            sql += " FROM     BudgetDetails b2 "
            sql += " INNER JOIN BudgetVersions ON b2.PlanYear = BudgetVersions.PlanYear "
            sql += " AND b2.Version = BudgetVersions.Version"
            sql += "  WHERE(b2.FundCenter_FundCenterCode = budgetdetails.fundcenter_fundcentercode)"
            sql += " AND (b2.Cmmt_CmmtCode = cmmtCode) "
            sql += " AND (b2.PlanYear = " & Session("year1") & ") "
            sql += " AND (b2.Version = '" & Session("version1") & "') "
            sql += " AND (BudgetVersions.PlanYear = " & Session("year1") & ") "
            sql += " AND (BudgetVersions.Version = '" & Session("version1") & "') "
            sql += " AND (b2.Id = budgetdetails.Id)"
            sql += " ) as amount1"

        Else
            sql += " ,0 as amount1"
        End If
        If Session("version2") <> "" And Session("year2") <> "" Then
            sql += " ,(SELECT ISNULL(SUM(b2.Amount),0)"
            sql += " FROM     BudgetDetails b2 "
            sql += " INNER JOIN BudgetVersions ON b2.PlanYear = BudgetVersions.PlanYear "
            sql += " AND b2.Version = BudgetVersions.Version"
            sql += "  WHERE(b2.FundCenter_FundCenterCode = budgetdetails.fundcenter_fundcentercode)"
            sql += " AND (b2.Cmmt_CmmtCode = cmmtCode) "
            sql += " AND (b2.PlanYear = " & Session("year2") & ") "
            sql += " AND (b2.Version = '" & Session("version2") & "') "
            sql += " AND (BudgetVersions.PlanYear = " & Session("year2") & ") "
            sql += " AND (BudgetVersions.Version = '" & Session("version2") & "') "
            sql += " AND (b2.Id = budgetdetails.Id)"
            sql += " ) as amount2"

        Else
            sql += " ,0 as amount2"
        End If
        If Session("version3") <> "" And Session("year3") <> "" Then
            sql += " ,(SELECT ISNULL(SUM(b2.Amount),0)"
            sql += " FROM     BudgetDetails b2 "
            sql += " INNER JOIN BudgetVersions ON b2.PlanYear = BudgetVersions.PlanYear "
            sql += " AND b2.Version = BudgetVersions.Version"
            sql += "  WHERE(b2.FundCenter_FundCenterCode = budgetdetails.fundcenter_fundcentercode)"
            sql += " AND (b2.Cmmt_CmmtCode = cmmtCode) "
            sql += " AND (b2.PlanYear = " & Session("year3") & ") "
            sql += " AND (b2.Version = '" & Session("version3") & "') "
            sql += " AND (BudgetVersions.PlanYear = " & Session("year3") & ") "
            sql += " AND (BudgetVersions.Version = '" & Session("version3") & "') "
            sql += " AND (b2.Id = budgetdetails.Id)"
            sql += " ) as amount3"

        Else
            sql += " ,0 as amount3"
        End If
        If Session("version4") <> "" And Session("year4") <> "" Then
            sql += " ,(SELECT ISNULL(SUM(b2.Amount),0)"
            sql += " FROM     BudgetDetails b2 "
            sql += " INNER JOIN BudgetVersions ON b2.PlanYear = BudgetVersions.PlanYear "
            sql += " AND b2.Version = BudgetVersions.Version"
            sql += "  WHERE(b2.FundCenter_FundCenterCode = budgetdetails.fundcenter_fundcentercode)"
            sql += " AND (b2.Cmmt_CmmtCode = cmmtCode) "
            sql += " AND (b2.PlanYear = " & Session("year4") & ") "
            sql += " AND (b2.Version = '" & Session("version4") & "') "
            sql += " AND (BudgetVersions.PlanYear = " & Session("year4") & ") "
            sql += " AND (BudgetVersions.Version = '" & Session("version4") & "') "
            sql += " AND (b2.Id = budgetdetails.Id)"
            sql += " ) as amount4"

        Else
            sql += " ,0 as amount4"
        End If
        If Session("version5") <> "" And Session("year5") <> "" Then
            sql += " ,(SELECT ISNULL(SUM(b2.Amount),0)"
            sql += " FROM     BudgetDetails b2 "
            sql += " INNER JOIN BudgetVersions ON b2.PlanYear = BudgetVersions.PlanYear "
            sql += " AND b2.Version = BudgetVersions.Version"
            sql += "  WHERE(b2.FundCenter_FundCenterCode = budgetdetails.fundcenter_fundcentercode)"
            sql += " AND (b2.Cmmt_CmmtCode = cmmtCode) "
            sql += " AND (b2.PlanYear = " & Session("year5") & ") "
            sql += " AND (b2.Version = '" & Session("version5") & "') "
            sql += " AND (BudgetVersions.PlanYear = " & Session("year5") & ") "
            sql += " AND (BudgetVersions.Version = '" & Session("version5") & "') "
            sql += " AND (b2.Id = budgetdetails.Id)"
            sql += " ) as amount5"

        Else
            sql += " ,0 as amount5"
        End If

        sql += " from budgetdetails, Cmmts, fundcenters, FctH "
        sql += " WHERE budgetdetails.cmmt_cmmtCode = Cmmts.cmmtCode And budgetdetails.fundcenter_fundcentercode = fundcenters.fundcentercode"
        sql += " AND fundcenters.fundcentercode = FctH.fundcenter_fundcentercode"
        sql += " and 1 = 1 "

        'sql += " AND Cmmts.formid IN ('F01','F02','F03') "
        sql += "AND Cmmts.CmmtCode IN ( " + _
                    "SELECT cmmt_cmmtcode from CmmtHierarchies where ParentCmmtHierarchy_Id in ( " + _
                        "SELECT Id FROM CmmtHierarchies " + _
                            "WHERE Level = 2 and PlanYear=" + Session("year") + " and CmmtGroup=1 " + _
                            "AND (Description = 'เงินเดือนและค่าจ้าง' " + _
                                "or Description = 'ค่าใช้สอย' " + _
                                "or Description = 'วัสดุ' " + _
                                "or Description = 'เงินตอบแทน' " + _
                                "or Description = 'เงินอุดหนุน' " + _
                                "or Description = 'รายจ่ายอื่นๆ')))"

        sql += " AND fundcenters.PlanYear = " & Session("year")
        sql += " AND Cmmts.PlanYear = " & Session("year")
        sql += " AND FctH.PlanYear = " & Session("year")

        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
                End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode  between '" & Session("CmmtCodeS") & "' and '" & Session("CmmtCodeE") & "'"
                    End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeS") & "'"
                    End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeE") & "'"
                    End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
                    End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and cmmtcode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
                    End If


                End If
                'sql += " and BudgetDetails.Version in (" & version1 & ")"
        sql += " ORDER BY CONVERT(VARCHAR,FctH.grouppath)"

        Dim txt As String
                'เพิ่ม SQL filter ตามหน้าจอ
        adapter.GetDataByFilter(datatable, sql)
                'Response.Write(datatable.Count)
        Dim FundCenterCode, CmmtCode, Description, project, BId
        Dim Amount1, Amount2, Amount3, Amount4, Amount5, Amount_Diff, Amount_Diff_Per As Decimal
        Dim r As DataRow
        For Each row As Data_Report.R07Row In datatable
            FundCenterCode = row.fundcentercode
            CmmtCode = row.cmmtcode
            Description = row.Description
            project = row.project
            BId = row.Id

            'txt = txt & Left(row.fundcentercode, 4)
                    'If txt = "9239" Then
                    '    sql = sql
                    'End If


                    'If Session("version1") <> "" And Session("year1") <> "" Then
                    '    Amount1 = Val(adapter.GetSum(FundCenterCode, CmmtCode, planyear1, version1, BId))
                    'End If
                    'If Session("version2") <> "" And Session("year2") <> "" Then
                    '    Amount2 = Val(adapter.GetSum(FundCenterCode, CmmtCode, planyear2, version2, BId))
                    'End If
                    'If Session("version3") <> "" And Session("year3") <> "" Then
                    '    Amount3 = Val(adapter.GetSum(FundCenterCode, CmmtCode, planyear3, version3, BId))
                    'End If
                    'If Session("version4") <> "" And Session("year4") <> "" Then
                    '    Amount4 = Val(adapter.GetSum(FundCenterCode, CmmtCode, planyear4, version4, BId))
                    'End If
                    'If Session("version5") <> "" And Session("year5") <> "" Then
                    '    Amount5 = Val(adapter.GetSum(FundCenterCode, CmmtCode, planyear5, version5, BId))
                    'End If

            Amount1 = row.amount1
            Amount2 = row.amount2
            Amount3 = row.amount3
            Amount4 = row.amount4
            Amount5 = row.amount5


            If Session("version1") <> "" And Session("year1") <> "" Then
                Amount_Diff = 0
                Amount_Diff_Per = 0
                    End If
            If Session("version2") <> "" And Session("year2") <> "" Then
                Amount_Diff = Amount2 - Amount1
                Try
                    If (Amount1 <> 0) Then
                        Amount_Diff_Per = (100 * Math.Abs(Amount2 - Amount1)) / Amount1
                    End If
                Catch ex As Exception
                    logger.Error(ex.Message)
                    logger.Error(ex.StackTrace)
                End Try

                    End If
            If Session("version3") <> "" And Session("year3") <> "" Then
                Amount_Diff = Amount3 - Amount2
                Try
                    If (Amount2 <> 0) Then
                        Amount_Diff_Per = (100 * Math.Abs(Amount3 - Amount2)) / Amount2
                    End If
                Catch ex As Exception
                    logger.Error(ex.Message)
                    logger.Error(ex.StackTrace)
                End Try
                    End If
            If Session("version4") <> "" And Session("year4") <> "" Then
                Amount_Diff = Amount4 - Amount3
                Try
                    If (Amount3 <> 0) Then
                        Amount_Diff_Per = (100 * Math.Abs(Amount4 - Amount3)) / Amount3
                    End If
                Catch ex As Exception
                    logger.Error(ex.Message)
                    logger.Error(ex.StackTrace)
                End Try
                    End If
            If Session("version5") <> "" And Session("year5") <> "" Then
                Amount_Diff = Amount5 - Amount4
                Try
                    If (Amount4 <> 0) Then
                        Amount_Diff_Per = (100 * Math.Abs(Amount5 - Amount4)) / Amount4
                    End If
                Catch ex As Exception
                    logger.Error(ex.Message)
                    logger.Error(ex.StackTrace)
                End Try
                    End If


            r = dt.NewRow()
                    'if have budget = 0 not show in report
            Dim amounttemp As Double = Amount1 + Amount2 + Amount3 + Amount4 + Amount5
            If amounttemp <> 0 And amounttemp <> Nothing Then
                r("fundcentercode") = FundCenterCode
                r("cmmtcode") = CmmtCode
                r("Description") = Description
                r("project") = project
                r("amount1") = Amount1
                r("amount2") = Amount2
                r("amount3") = Amount3
                r("amount4") = Amount4
                r("amount5") = Amount5
                r("amount_diff") = Amount_Diff
                r("amount_diff_per") = Amount_Diff_Per
                dt.Rows.Add(r)
            End If
        Next
                'Response.Write(sql)
        Return dt
    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

End Class
